Imports ADOConnection
Imports System.Data
Imports System.Data.SqlClient

Public Class CLoaiThuoc
    Private m_MaLoaiThuoc As String
    Private m_TenLoaiThuoc As String
    Private m_MaCha As String

#Region "Property"
    Property MaLoaiThuoc() As String
        Get
            Return m_MaLoaiThuoc
        End Get
        Set(ByVal value As String)
            m_MaLoaiThuoc = value
        End Set
    End Property

    Property TenLoaiThuoc() As String
        Get
            Return m_TenLoaiThuoc
        End Get
        Set(ByVal value As String)
            m_TenLoaiThuoc = value
        End Set
    End Property

    Property MaCha() As String
        Get
            Return m_MaCha
        End Get
        Set(ByVal value As String)
            m_MaCha = value
        End Set
    End Property

#End Region

End Class

Public Class CLoaiThuocs
    Public Shared Function GetLoaiThuocListByMaCha(ByVal macha As String) As ArrayList
        Dim aryItems As New ArrayList

        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT ma_loaithuoc, ten_loaithuoc, ma_cha" & _
                " from tblLOAITHUOC" & _
                " where ma_cha = '" & macha & "'" & _
                "  order by ma_loaithuoc"
        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Try
            objReader = objConnection.GetDataReader(objCommand, True)

            While objReader.Read
                Dim objItem As New CLoaiThuoc
                With objItem
                    .MaLoaiThuoc = objReader.GetString(0)
                    .TenLoaiThuoc = objReader.GetString(1)
                    .MaCha = objReader.GetString(2)
                End With
                aryItems.Add(objItem)
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return aryItems

    End Function

    Public Shared Function GetLoaiThuocByMa(ByVal ma_loaithuoc As String) As CLoaiThuoc
        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT ma_loaithuoc, ten_loaithuoc, ma_cha" & _
                " from tblLOAITHUOC" & _
                " where ma_loaithuoc = '" & ma_loaithuoc & "'"

        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objItem As New CLoaiThuoc
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                With objItem
                    .MaLoaiThuoc = objReader.GetString(0)
                    .TenLoaiThuoc = objReader.GetString(1)
                    .MaCha = objReader.GetString(2)
                End With
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return objItem

    End Function

    Public Shared Function GetLoaiThuocByTen(ByVal ten_loaithuoc As String) As CLoaiThuoc
        Dim strSQL As String
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility

        strSQL = "SELECT ma_loaithuoc, ten_loaithuoc, ma_cha" & _
                " from tblLOAITHUOC" & _
                " where ten_loaithuoc like N'" & ten_loaithuoc & "'"

        Dim objReader As CDataReader = Nothing
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strSQL)
        Dim objItem As New CLoaiThuoc
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                With objItem
                    .MaLoaiThuoc = objReader.GetString(0)
                    .TenLoaiThuoc = objReader.GetString(1)
                    .MaCha = objReader.GetString(2)
                End With
            End While

        Catch ex As System.Exception
            MsgBox(strSQL)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try

        Return objItem
    End Function

    Public Shared Function InsertLoaiThuoc(ByVal objLT As CLoaiThuoc) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sInsert As String = "INSERT INTO tblLOAITHUOC" & _
        " (ma_loaithuoc,ten_loaithuoc,ma_cha) VALUES(" & _
        objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", True) & _
        objDBUtility.GetParamPlaceHolder("TenLoaiThuoc", True) & _
        objDBUtility.GetParamPlaceHolder("MaCha", False) & _
        ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objLT.MaLoaiThuoc, objLT.MaLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("TenLoaiThuoc", DbType.String, objLT.TenLoaiThuoc, objLT.TenLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaCha", DbType.String, objLT.MaCha, objLT.MaCha.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Public Shared Function UpdateLoaiThuoc(ByVal objLT As CLoaiThuoc) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sUpdate As String = "UPDATE tblLOAITHUOC SET ten_loaithuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("TenLoaiThuoc", True)
        sUpdate &= " ma_cha = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("MaCha", False)
        sUpdate &= " WHERE ma_loaithuoc = " & _
        objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", False)

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sUpdate)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objLT.MaLoaiThuoc, objLT.MaLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("TenLoaiThuoc", DbType.String, objLT.TenLoaiThuoc, objLT.TenLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaCha", DbType.String, objLT.MaCha, objLT.MaCha.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Public Shared Function DeleteLoaiThuoc(ByVal objLT As CLoaiThuoc) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        Dim sDelete As String = "DELETE FROM tblLOAITHUOC WHERE (ma_loaithuoc = "
        sDelete &= objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", False)
        sDelete &= ")"
        
        Dim objCommand As IDbCommand = objConnection.CreateCommand(sDelete)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objLT.MaLoaiThuoc, objLT.MaLoaiThuoc.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function

    Public Shared Function UpdateLoaiThuoc(ByVal objLT As CLoaiThuoc, ByVal ma_loaithuocmoi As String) As Long
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sUpdate As String = "UPDATE tblLOAITHUOC SET ten_loaithuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("TenLoaiThuoc", True)
        sUpdate &= " ma_loaithuoc = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("MaLoaiThuocMoi", True)
        sUpdate &= " ma_cha = "
        sUpdate &= objDBUtility.GetParamPlaceHolder("MaCha", False)
        sUpdate &= " WHERE ma_loaithuoc = " & _
        objDBUtility.GetParamPlaceHolder("MaLoaiThuoc", False)

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sUpdate)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuoc", DbType.String, objLT.MaLoaiThuoc, objLT.MaLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("TenLoaiThuoc", DbType.String, objLT.TenLoaiThuoc, objLT.TenLoaiThuoc.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaCha", DbType.String, objLT.MaCha, objLT.MaCha.Length))
                .Parameters.Add(objDBUtility.CreateParameter("MaLoaiThuocMoi", DbType.String, ma_loaithuocmoi, ma_loaithuocmoi.Length))
                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try

        Return lRowsAffected
    End Function
End Class

